package com.gt.quality.system.service.impl;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.json.JSONUtil;
import com.gt.quality.system.service.LocalDatabaseProxy;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 万里悲秋常作客,百年多病独登台
 *
 * @author : makeJava
 */
@Component
@Slf4j
public class LocalDatabaseProxyImpl implements LocalDatabaseProxy {


    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String dbUsername;

    @Value("${spring.datasource.password}")
    private String dbPassword;


    /**
     * 获取数据库的表名和注释
     *
     * @param databaseName 数据库名称
     * @return 表名和注释的JSON字符串
     */
    @Override
    public String getTablesAndComments(String databaseName) {
        String sql = "SELECT TABLE_NAME, TABLE_COMMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + databaseName + "'";
        List<Map<String, Object>> resultList = executeSql(sql);
        return JSONUtil.toJsonStr(resultList);
    }


    /**
     * 通用执行Ai 生成的SQL
     *
     * @param sql sql
     * @return List<Map < String, Object>>
     */
    @Override
    public List<Map<String, Object>> executeSql(String sql) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
             Statement statement = connection.createStatement();
             ResultSet resultSet = statement.executeQuery(sql)) {

            coreSqlHandler(resultList, resultSet);
        } catch (Exception e) {
            log.error("执行SQL失败: {}", e.getMessage(), e);
        }
        return resultList;
    }


    @Override
    public void coreSqlHandler(List<Map<String, Object>> resultList, ResultSet resultSet) throws SQLException {
        int columnCount = resultSet.getMetaData().getColumnCount();
        while (resultSet.next()) {
            Map<String, Object> row = new HashMap<>();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = resultSet.getMetaData().getColumnName(i);
                Object columnValue = resultSet.getObject(i);
                row.put(columnName, columnValue);
            }
            resultList.add(row);
        }
    }

    /**
     * 获取指定表的列名和注释
     *
     * @param tableName 表名
     * @return 列名和注释的JSON字符串
     */
    @Override
    public String getColumnsAndComments(String databaseName, String tableName) {
        String sql = "SELECT COLUMN_NAME, COLUMN_COMMENT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = '" + databaseName + "' AND TABLE_NAME = '" + tableName + "'";
        List<Map<String, Object>> resultList = executeSql(sql);
        return JSONUtil.toJsonStr(resultList);
    }

    /**
     * 获取指定表的建表语句DDL
     *
     * @param databaseName 数据库名称
     * @param tableNameStr 表名
     * @return 建表语句的JSON字符串
     */
    @Override
    public String getTableDdl(String databaseName, String tableNameStr) {
        // 干扰表白名单--屏蔽掉
        List<String> whiteList = Arrays.asList("sys_role_dept", "sys_role_menu", "sys_oper_log", "file_export_template");
        String[] split = tableNameStr.split(",");
        StringBuilder stringBuilder = new StringBuilder();
        List<String> sqlList = new ArrayList<>();
        for (String tableName : split) {
            // 屏蔽掉干扰表
            if (whiteList.contains(tableName)) {
                continue;
            }
            String sql = "SHOW CREATE TABLE `" + databaseName + "`.`" + tableName + "`";
            sqlList.add(sql);
        }
        List<Map<String, Object>> resultList = executeSqlList(sqlList);
        if (CollUtil.isNotEmpty(resultList)) {
            for (Map<String, Object> mapRow : resultList) {
                String createTableStatement = (String) mapRow.get("Create Table");
                stringBuilder.append(createTableStatement);
            }
            return stringBuilder.toString();
        }
        return null;
    }

    /**
     * 执行SQL语句并返回结果
     *
     * @param sqlList SQL语句
     * @return 结果列表
     */
    @Override
    public List<Map<String, Object>> executeSqlList(List<String> sqlList) {
        List<Map<String, Object>> resultList = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection(dbUrl, dbUsername, dbPassword);
             Statement statement = connection.createStatement();
        ) {
            for (String sql : sqlList) {
                ResultSet resultSet = statement.executeQuery(sql);
                coreSqlHandler(resultList, resultSet);
            }
        } catch (Exception e) {
            log.error("执行SQL失败: {}", e.getMessage(), e);
        }
        return resultList;
    }









}

